package com.etc.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.etc.dao.BussiteDao;
import com.etc.entity.BusSites;
import com.etc.entity.Bussite;
import com.etc.entity.Site;
import com.etc.util.DBUtil;
import com.etc.util.PageData;
   /**
    * 中间站点增删改查具体实现
    * @author xst
    *
    */
public class BussiteDaoImpl implements BussiteDao {
	/**
	 * 查询所有车辆站点信息
	 */
	@Override
	public List<Bussite> queryBussites() {
		List<Bussite> list = new ArrayList<>();
		Bussite bussite = new Bussite();
		CachedRowSet crs = DBUtil.execQuery("select * from bussite");
		try {
			while (crs.next()) {
			int bs_id=crs.getInt(1);
			int b_id=crs.getInt(2);
			int s_id=crs.getInt(3);
			int s_location=crs.getInt(4);
			bussite=new Bussite(bs_id, b_id, s_id, s_location);
			list.add(bussite);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 根据车辆编号查询车辆站点信息
	 */
	@Override
	public List<Bussite> queryBussitesByB_id(int b_id) {
		List<Bussite> list = new ArrayList<>();
		Bussite bussite = new Bussite();
		CachedRowSet crs = DBUtil.execQuery("select * from bussite where b_id=? order by s_location",b_id);
		try {
			while (crs.next()) {
			int bs_id=crs.getInt(1);
			int b_id1=crs.getInt(2);
			int s_id=crs.getInt(3);
			int s_location=crs.getInt(4);
			bussite=new Bussite(bs_id, b_id1, s_id, s_location);
			list.add(bussite);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 增加车辆站点信息
	 */
	@Override
	public boolean addBussite(Bussite bussite) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("insert into bussite values(null,?,?,?)", bussite.getB_id(),bussite.getS_id(),bussite.getS_location());
	}
	/**
	 * 修改车辆站点信息
	 */
	@Override
	public boolean updatBussite(Bussite bussite) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("update bussite set b_id=?,s_id=?,s_location=? where bs_id=?", bussite.getB_id(),bussite.getS_id(),bussite.getS_location(),bussite.getBs_id());
	}
	/**
	 * 删除车辆站点信息
	 */
	@Override
	public boolean deleBussite(int bs_id) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("delete from bussite where bs_id=?", bs_id);
	}
	/**
	 * 查询站点信息关联车辆
	 */
	@Override
	public List<BusSites> queryAllbusSite() {
		String sql="SELECT a.s_id,a.s_name,b.b_id,b.s_location FROM site a INNER JOIN bussite b ON a.s_id=b.s_id";
		CachedRowSet set = DBUtil.execQuery(sql);
		List<BusSites> list = new ArrayList<BusSites>();
		BusSites sites=null;
		try {
			while (set.next()) {
				int id = set.getInt(1);
				String name = set.getString(2);
				int bid = set.getInt(3);
				int location = set.getInt(4);
				 sites = new BusSites(id, name, bid, location);
				 list.add(sites);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public PageData queryLimtSite(int pageNo, int pageSize) {
		String sql="SELECT a.s_id,a.s_name,b.b_id,b.s_location FROM site a INNER JOIN bussite b ON a.s_id=b.s_id LIMIT ?,?";
		CachedRowSet set = DBUtil.execQuery(sql,(pageNo-1)*pageSize,pageSize);
		List<BusSites> list = new ArrayList<BusSites>();
		BusSites sites=null;
		try {
			while (set.next()) {
				int id = set.getInt(1);
				String name = set.getString(2);
				int bid = set.getInt(3);
				int location = set.getInt(4);
				 sites = new BusSites(id, name, bid, location);
				 list.add(sites);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//获得分页的总条数
		String sql1="SELECT count(*) FROM site a INNER JOIN bussite b ON a.s_id=b.s_id";
		CachedRowSet set2 = DBUtil.execQuery(sql1);
		int totalCount=0;
	      try {
			while (set2.next()) {
				totalCount = set2.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	      PageData pageData = new PageData(list, pageNo, pageSize, totalCount);
		return pageData;
	}
	@Override
	public boolean deleBussite(int b_id, int s_location) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("delete from bussite where b_id=? and s_location=?", b_id,s_location);
	}
	@Override
	public int queryBussiteId(int b_id, int s_location) {
		String sql="SELECT bs_id FROM bussite WHERE b_id=?  AND s_location=?";
		CachedRowSet set = DBUtil.execQuery(sql, b_id,s_location);
		int bs_id=0;
		try {
			while (set.next()) {
				bs_id = set.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bs_id;
	}
	@Override
	public BusSites queryBussite(int b_id, int s_id, int s_location) {
		String sql="SELECT * FROM bussite WHERE b_id=? AND s_id=? AND s_location=?";
		CachedRowSet set = DBUtil.execQuery(sql, b_id,s_id,s_location);
		BusSites sites=null;
		try {
			while (set.next()) {
				int id = set.getInt(1);
				String name = set.getString(2);
				int bid = set.getInt(3);
				int location = set.getInt(4);
				 sites = new BusSites(id, name, bid, location);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sites;
	}
	
	
}
